using System;
using System.Data;
using System.Collections.Generic;
using System.Text;

namespace Auto.Data.Systems.DAL
{
    /// <summary>
    /// 
    /// </summary>
    public class PagerList
    {
        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="tablePrimaryKey">The table primary key.</param>
        /// <param name="getColName">Name of the get col.</param>
        /// <param name="where">The where.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="pageIndex">Index of the page.</param>
        /// <param name="orderByName">Name of the order by.</param>
        /// <param name="orderByType">Type of the order by.</param>
        /// <returns></returns>
        public DataTable GetList(string tableName, string tablePrimaryKey, string getColName, string where, int pageSize, int pageIndex, string orderByName, string orderByType)
        {
            StringBuilder query = new StringBuilder();

            query = new StringBuilder(string.Format(@"SELECT TOP {0} {1} FROM (SELECT TOP {2} {1} FROM {3}
                                                                                   ", pageSize, getColName, pageIndex, tableName));

            if (!string.IsNullOrEmpty(where))
            {
                query.AppendFormat(" WHERE {0}", where);
            }

            query.AppendFormat(" ORDER BY {0} {1},{2} {1}", orderByName, orderByType.ToUpper() == "ASC" ? "DESC" : "ASC", tablePrimaryKey);

            query.Append(") tbl");

            query.AppendFormat(" ORDER BY {0} {1},{2} {1}", orderByName, orderByType, tablePrimaryKey);
            DataTable dt = Auto.Data.DBUtility.DbHelper.ExecuteQuery(query.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="getColName">Name of the get col.</param>
        /// <param name="where">The where.</param>
        /// <param name="orderByName">Name of the order by.</param>
        /// <param name="orderByType">Type of the order by.</param>
        /// <returns></returns>
        public DataTable GetList(string tableName, string getColName, string where,string orderByName, string orderByType)
        {
            StringBuilder query = new StringBuilder();

            query = new StringBuilder(string.Format(@"SELECT {0} FROM {1}",  getColName, tableName));

            if (!string.IsNullOrEmpty(where))
            {
                query.AppendFormat(" WHERE {0}", where);
            }
            if (!string.IsNullOrEmpty(orderByName) && !string.IsNullOrEmpty(orderByType))
            {
                query.AppendFormat(" ORDER BY {0} {1}", orderByName, orderByType);
            }
            
            DataTable dt = Auto.Data.DBUtility.DbHelper.ExecuteQuery(query.ToString()).Tables[0];
            return dt;
        }


        /// <summary>
        /// Counts the specified table name.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public int Count(string tableName, string where)
        {
            //where = "";// "RecID IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)";
            //tableName = "_UsersLoginLog";

            StringBuilder query = new StringBuilder();

            query.AppendFormat("SELECT COUNT(1) FROM {0}", tableName);

            if (!string.IsNullOrEmpty(where))
            {
                query.AppendFormat(@" WHERE {0}", where);
            }

            int result = (int)Auto.Data.DBUtility.DbHelper.ExecuteScalar(query.ToString());
            return result;
        }
    }
}
